package com.ctl.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SelectMethod {

    /**
     * @Title: SelectMethod.java
     * @Package com.ctl.test
     * @Description: 获取数据库连接
     * @author 陈泰来
     * @date 2017/6/8 上午11:16:19
     * @version V1.0
     */
    public Connection getConnection() {
        Connection conn = null;
        try {
            String url = "jdbc:sqlserver://localhost:1433;DataBaseName=sqlserverdb";
            String user = "sa";
            String password = "123";
            String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * @Title: SelectMethod.java
     * @Package com.ctl.test
     * @Description: 根据sql获取学生信息
     * @author 陈泰来
     * @date 2017/6/8 上午11:16:19
     * @version V1.0
     */
    public List<Student> getStudents(String sql) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        @SuppressWarnings({"unchecked", "rawtypes"})
        List<Student> list_student = new ArrayList();
        conn = this.getConnection();
        try {
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setStuId(rs.getInt(1));
                student.setStuName(rs.getString(2));
                student.setStuSex(rs.getString(3));
                student.setStuNumber(rs.getString(4));
                list_student.add(student);
            }
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return list_student;
    }

    /**
     * @Title: SelectMethod.java
     * @Package com.ctl.test
     * @Description: 获取查询结果总数
     * @author 陈泰来
     * @date 2017/6/8 上午11:16:19
     * @version V1.0
     */
    public int getCountInfo() {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int count = 0;
        conn = this.getConnection();
        try {
            pst = conn.prepareStatement("select cout(*) from student");
            rs = pst.executeQuery();
            count = rs.getInt(0);
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return count;
    }

    /**
     * @Title:SelectMethod.java
     * @Package com.ctl.test
     * @Description: 插入学生信息
     * @author 陈泰来
     * @date 2017/6/8 上午11:16:19
     * @version V1.0
     */
    public String insertStudentInfo(String stuName, String stuSex, String stuNumber) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int count = 0;
        conn = this.getConnection();
        try {
            pst = conn.prepareStatement("insert into Student(stuName,stuSex,StuNumber) values (?,?,?)");
            pst.setString(1, stuName);
            pst.setString(2, stuSex);
            pst.setString(3, stuNumber);
            count = pst.executeUpdate();
            System.out.println(count + "条数据受影响!");
            conn.commit();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return "插入成功!";
    }

    /**
     * @Title:SelectMethod.java
     * @Package com.ctl.test
     * @Description: 根据学生Id获取学生对象
     * @author 陈泰来
     * @date 2017/6/8 上午11:16:19
     * @version V1.0
     */
    public Student getStuInfoById(int stuId) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        conn = this.getConnection();
        Student student = new Student();
        try {
            pst = conn.prepareStatement("select * from student where stuId=?");
            pst.setInt(1, stuId);
            rs = pst.executeQuery();
            while (rs.next()) {
                student.setStuId(rs.getInt(1));
                student.setStuName(rs.getString(2));
                student.setStuSex(rs.getString(3));
                student.setStuNumber(rs.getString(4));
            }
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return student;
    }

    /**
     * @Title:SelectMethod.java
     * @Package com.ctl.test
     * @Description: 更新学生信息（修改）
     * @author 陈泰来
     * @date 2017/6/8 上午11:16:19
     * @version V1.0
     */
    public int updateStudentInfo(Student stu) {
        int count = 0;
        Connection conn = null;
        PreparedStatement pst = null;
        conn = this.getConnection();
        try {
            pst = conn.prepareStatement("update Student set stuName=?,stuSex=?,stuNumber=? where stuId=?");
            pst.setString(1, stu.getStuName());
            pst.setString(2, stu.getStuSex());
            pst.setString(3, stu.getStuNumber());
            pst.setInt(4, stu.getStuId());
            count = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return count;
    }

    public int deleteStudentInfo(int stuId) {
        // TODO 自动生成的方法存根
        int count = 0;
        PreparedStatement pst = null;
        Connection conn = null;
        conn = this.getConnection();
        try {
            String sql = "delete from Student where stuId=?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, stuId);
            count = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return count;
    }
}
